import requests
from datetime import datetime, timedelta
import sqlite3
import json

# 假设这是后台 API 的 URL，你需要替换为实际的 URL
API_URL = "http://127.0.0.1:5000/api/query_dwzb_eta"
# 假设这是提交数据的 API URL
SUBMIT_API_URL = "http://127.0.0.1:5000/api/daily_stats"
# 数据库路径
DB_PATH = r"‪D:\Users\c19471\Desktop\tianeye\database\test.db"

# 获取当前日期和班次信息
# 根据当前时间判断日期和班次，若时间在 8:30 之前，日期减一天且班次为夜班；若时间在 20:30 之后，班次为夜班；否则为白班
def get_date_and_shift():
    now = datetime.now()
    hour = now.hour + now.minute / 60
    today = datetime.today()
    if hour < 9:
        today = today - timedelta(days=1)
        shift = "N"
    elif hour > 21:
        shift = "N"
    else:
        shift = "D"
    formatted_date = today.strftime("%Y%m%d")
    return formatted_date, shift

# 检查数据库中是否已经存在指定日期、班次和产品的数据
# 参数:
#   formatted_date: 格式化后的日期，格式为 YYYYMMDD
#   shift: 班次，值为 "N" 或 "D"
#   product: 产品名称
# 返回值:
#   若数据存在返回 True，否则返回 False
def check_data_existence(formatted_date, shift, product):
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        check_query = "SELECT 1 FROM alldata WHERE date =? AND shift =? AND BinFileName =?"
        cursor.execute(check_query, (formatted_date, shift, product))
        result = cursor.fetchone()
        conn.close()
        return result is not None
    except sqlite3.Error as e:
        print(f"数据库操作出错: {e}")
        return False

# 根据 API 返回的 aggtj_data 和 sumbin_data 计算各项指标
# 参数:
#   aggtj_data: API 返回的聚合统计数据列表
#   sumbin_data: API 返回的合并仓数据列表
# 返回值:
#   依次返回总产量、正常产出、平均效率、入库效率、合并档产出、漏电合并档产出、漏电失效产出和 DLCC 产出
def calculate_metrics(aggtj_data, sumbin_data):
    # 计算总产量
    total_output = sum(item.get('total_sum_ivgrade', 0) for item in aggtj_data)

    # 计算正常产出
    normal_output = sum(item.get('total_sum_ivgrade', 0) for item in aggtj_data if 'Eta' in item.get('Class', '') and 'Eta Fail' not in item.get('Class', ''))

    # 计算平均效率
    eta_items = [item for item in aggtj_data if 'Eta' in item.get('Class', '') and 'Eta Fail' not in item.get('Class', '')]
    avg_efficiency_numerator = sum(item.get('total_sum_ivgrade', 0) * item.get('etacs', 0) for item in eta_items)
    avg_efficiency = (avg_efficiency_numerator / normal_output) if normal_output else 0

    # 计算入库效率
    def convert_to_float(class_str):
        """
        将包含 'Eta' 和 '%' 的字符串转换为浮点数
        参数:
            class_str: 包含 'Eta' 和 '%' 的字符串
        返回值:
            转换后的浮点数，若转换失败则返回 0
        """
        cleaned_str = class_str.replace('Eta', '').replace('%', '')
        try:
            return float(cleaned_str) if '%' in class_str else float(cleaned_str)
        except ValueError:
            return 0

    valid_eta_items = [item for item in aggtj_data if 'Eta' in item.get('Class', '') and 'Eta Fail' not in item.get('Class', '')]
    storage_efficiency_numerator = sum(item.get('total_sum_ivgrade', 0) * convert_to_float(item.get('Class', '')) for item in valid_eta_items)
    storage_efficiency = (storage_efficiency_numerator / normal_output) if normal_output else 0

    # 计算合并档产出
    eta_classes = sorted([item for item in aggtj_data if 'Eta' in item.get('Class', '') and 'Eta Fail' not in item.get('Class', '')], key=lambda x: x.get('Class', ''))
    merge_classes = ['UNG', 'IRev2-B', 'Rsh-B']
    if eta_classes:
        merge_classes.append(eta_classes[0].get('Class', ''))
    merge_output = sum(item.get('total_sum_ivgrade', 0) for item in aggtj_data if any(cls in item.get('Class', '') for cls in merge_classes))

    # 计算漏电合并档产出
    leak_output = sum(item.get('total_sum_ivgrade', 0) for item in aggtj_data if item.get('Class', '') == 'IRev2-B')

    # 计算漏电失效产出
    leak_fail = sum(item.get('total_sum_ivgrade', 0) for item in aggtj_data if item.get('Class', '') == 'IRev2 Fail')

    # 计算 DLCC 产出
    DLCC = sum(item.get('quantity', 0) for item in sumbin_data if item.get('binn') == 7)

    return total_output, normal_output, avg_efficiency, storage_efficiency, merge_output, leak_output, leak_fail, DLCC

# 主函数，处理 API 数据查询和提交流程
# 1. 获取当前日期和班次
# 2. 遍历产品列表，检查数据库中是否存在对应数据
# 3. 若数据不存在，则调用 API 查询数据
# 4. 计算各项指标并将数据提交到另一个 API
def get_product_list_from_json():
    json_path = r"D:\Users\c19471\Desktop\tianeye\static\json\alldata.json"
    
    try:
        with open(json_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
            for item in data:
                if item.get('name') == 'product_name':
                    return item.get('value').split(',')
    except FileNotFoundError:
        print(f"未找到文件: {json_path}")
    return []

def process_api_data():
    formatted_date, shift = get_date_and_shift()
##    formatted_date, shift = "20250609","N"
    # 调用函数获取产品列表
    start_date = datetime.strptime("20250713", "%Y%m%d")
    end_date = datetime.strptime("20250718", "%Y%m%d")

    current_date = start_date
    while current_date <= end_date:
        formatted_date=current_date.strftime("%Y%m%d")
        current_date += timedelta(days=1)
        for cs in ["D","N"]:
            shift=cs
            products = get_product_list_from_json()
            print(products)
            for product in products:
                if check_data_existence(formatted_date, shift, product):
                    print(f"产品 {product} 在 {formatted_date} {shift} 班次的数据已存在，跳过查询。")
                    continue

                form_data = {
                    "query_date": formatted_date,
                    "shift": shift,
                    "product": product
                }
                print(form_data)
                try:
                    # 发送 POST 请求到 API
                    response = requests.post(API_URL, json=form_data)

                    # 检查响应状态码
                    if response.status_code == 200:
                        # 解析 JSON 数据
                        data = response.json()
                        aggtj_data = data.get('aggtj_data', [])
                        sumbin_data = data.get('sumbin_data', [])
                        if not aggtj_data:
                            print(f"产品 {product} 返回的数据为空，跳过当前循环")
                            continue

                        total_output, _, avg_efficiency, storage_efficiency, merge_output, leak_output, leak_fail, DLCC = calculate_metrics(aggtj_data, sumbin_data)

                        # 准备提交的数据
                        submit_data = {
                            "date": formatted_date,
                            "shift": shift,
                            "BinFileName": product,
                            "sum_IvGrade": total_output,
                            "avg_eta": round(avg_efficiency, 3),
                            "ru_eta": round(storage_efficiency, 3),
                            "hbd": merge_output,
                            "dlcc": DLCC,
                            "IRev2_B": leak_output,
                            "IRev2_Fail": leak_fail
                        }

                        # 发送 POST 请求提交数据
                        submit_response = requests.post(SUBMIT_API_URL, json=submit_data)

                        if submit_response.status_code == 200:
                            print(f"产品 {product} 数据提交成功")
                        else:
                            print(f"产品 {product} 数据提交失败，状态码: {submit_response.status_code}")
                    else:
                        print(f"产品 {product} 请求失败，状态码: {response.status_code}")
                except requests.RequestException as e:
                    print(f"请求发生错误: {e}")

if __name__ == "__main__":
    process_api_data()
